package org.smart4j.framework.helper;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.smart4j.framework.util.CollectionUtil;
import org.smart4j.framework.util.PropsUtil;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.sql.*;
import java.util.*;

/**
 * Created by Administrator on 2016/12/10.
 * Description: 数据库操作助手类
 */
public class DatabaseHelper {

    private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseHelper.class);

    public static final QueryRunner QUERY_RUNNER = new QueryRunner();

    /**
     * ThreadLocal可以理解为一个隔离线程的容器，可以存放一些对象 *
     */
    private static final ThreadLocal<Connection> CONNECTION_HOLDER = new ThreadLocal<Connection>();

    /**
     * Apache DBCP *
     */
    private static final BasicDataSource DATA_SOURCE;

    private static final String DRIVER;
    private static final String URL;
    private static final String USERNAME;
    private static final String PASSWORD;

    static {
        Properties conf = PropsUtil.loadProps("config.properties");
        DRIVER = conf.getProperty("jdbc.driver");
        URL = conf.getProperty("jdbc.url");
        USERNAME = conf.getProperty("jdbc.username");
        PASSWORD = conf.getProperty("jdbc.password");

        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            LOGGER.error("Can not load jdbc driver!", e);
        }

        DATA_SOURCE = new BasicDataSource();
        DATA_SOURCE.setDriverClassName(DRIVER);
        DATA_SOURCE.setUrl(URL);
        DATA_SOURCE.setUsername(USERNAME);
        DATA_SOURCE.setPassword(PASSWORD);

    }

    public static void executeSqlFile(String filePath) {
        InputStream inputStream = Thread.currentThread()
                .getContextClassLoader().getResourceAsStream(filePath);
        BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
        try {
            String sql;
            while ((sql = reader.readLine()) != null) {
                executeUpdate(sql);
            }
        } catch (Exception e) {
            LOGGER.error("Execute sql file failure", e);
            throw new RuntimeException(e);
        }

    }

    /**
     * author: Wangminze
     * date: 2016/12/10
     * description: 获取数据库连接
     *
     * @return
     */
    private static Connection getConnection() {
        Connection conn = CONNECTION_HOLDER.get();
        if (conn == null) {
            try {
                //conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
                conn = DATA_SOURCE.getConnection();
            } catch (SQLException e) {
                LOGGER.error("Get connection failure", e);
                throw new RuntimeException(e);
            } finally {
                CONNECTION_HOLDER.set(conn);
            }
        }
        return conn;
    }

    /*
    private static void closeConnection(){
        Connection conn = CONNECTION_HOLDER.get();
        if (conn != null){
            try {
                conn.close();
            }catch (SQLException e){
                LOGGER.error("Close connection failure",e);
                throw new RuntimeException(e);
            }finally {
                CONNECTION_HOLDER.remove();
            }
        }
    }
*/

    /**
     * author: Wangminze
     * date: 2016/12/10
     * description: 查询实体列表
     *
     * @param entityClass
     * @param sql
     * @param params
     * @param <T>
     * @return
     */
    public static <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... params) {
        List<T> entityList;
        try {
            Connection conn = getConnection();
            entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(entityClass), params);
        } catch (SQLException e) {
            LOGGER.error("Query entity list failure", e);
            throw new RuntimeException(e);
        }/*finally{
            closeConnection();
        }*/
        return entityList;
    }

    /**
     * author: Wangminze
     * date: 2016/12/10
     * description: 查询单个实体
     *
     * @param entityClass
     * @param sql
     * @param params
     * @param <T>
     * @return
     */
    public static <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {
        T entity;
        try {
            Connection conn = getConnection();
            entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(entityClass), params);
        } catch (SQLException e) {
            LOGGER.error("Query entity list failure", e);
            throw new RuntimeException(e);
        }/*finally{
            closeConnection();
        }*/
        return entity;
    }

    /**
     * author: Wangminze
     * date: 2016/12/10
     * description: 执行查询语句，可实现多表关联查询
     *
     * @param sql
     * @param params
     * @return
     */
    private static List<Map<String, Object>> executeQuery(String sql, Object... params) {
        List<Map<String, Object>> result;
        try {
            Connection conn = getConnection();
            result = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);
        } catch (Exception e) {
            LOGGER.error("Execute query failure", e);
            throw new RuntimeException(e);
        }/*finally{
            closeConnection();
        }*/
        return result;
    }


    /**
     * author: Wangminze
     * date: 2016/12/10
     * description: 执行更新语句（Update，Insert，Delete）
     *
     * @param sql
     * @param params
     * @return 受影响的行数
     */
    private static int executeUpdate(String sql, Object... params) {
        int rows = 0;
        try {
            Connection conn = getConnection();
            rows = QUERY_RUNNER.update(conn, sql, params);
        } catch (SQLException e) {
            LOGGER.error("Execute update failure", e);
            throw new RuntimeException(e);
        }/*finally {
            closeConnection();
        }*/
        return rows;
    }

    /**
     * author: Wangminze
     * date: 2016/12/10
     * description: 新增实体
     *
     * @param entityClass
     * @param fieldMap
     * @param <T>
     * @return
     */
    public static <T> int insertEntity(Class<T> entityClass, Map<String, Object> fieldMap) {
        if (CollectionUtil.isEmpty(fieldMap)) {
            LOGGER.error("Can not insert entity:fieldMap is empty");
            return 0;
        }

        String sql = "INSERT INTO " + getTableName(entityClass);
        StringBuilder columns = new StringBuilder("(");
        StringBuffer values = new StringBuffer("(");
        for (String fieldName : fieldMap.keySet()) {
            columns.append(fieldName).append(", ");
            values.append("?, ");
        }
        columns.replace(columns.lastIndexOf(", "), columns.length(), ")");
        values.replace(values.lastIndexOf(", "), values.length(), ")");
        sql += columns + " VALUES " + values;

        Object[] params = fieldMap.values().toArray();
        return executeUpdate(sql, params);
    }

    /**
     * author: Wangminze
     * date: 2016/12/10
     * description: 更新实体
     *
     * @param entityClass
     * @param id
     * @param fieldMap
     * @param <T>
     * @return
     */
    public static <T> boolean updateEntity(Class<T> entityClass, long id, Map<String, Object> fieldMap) {
        if (CollectionUtil.isEmpty(fieldMap)) {
            LOGGER.error("Can not update entity:fieldMap is empty");
            return false;
        }

        String sql = "UPDATE " + getTableName(entityClass) + " SET ";
        StringBuilder columns = new StringBuilder();
        for (String fieldName : fieldMap.keySet()) {
            columns.append(fieldName).append("=?, ");
        }
        sql += columns.substring(0, columns.lastIndexOf(", ")) + " WHERE id = ?";

        List<Object> paramList = new ArrayList<Object>();
        paramList.addAll(fieldMap.values());
        paramList.add(id);
        Object[] params = paramList.toArray();

        return executeUpdate(sql, params) == 1;
    }

    /**
     * author: Wangminze
     * date: 2016/12/10
     * description: 删除实体
     * @param entityClass
     * @param id
     * @param <T>
     * @return
     */
    public static <T> boolean deleteEntity(Class<T> entityClass, long id) {
        String sql = "DELETE FROM " + getTableName(entityClass) + " WHERE id=?";
        return executeUpdate(sql, id) == 1;
    }

    private static String getTableName(Class<?> entityClass) {

        String beanName = entityClass.getSimpleName();
        String tableName = beanName.replace("Bean", "");
        return "t_" + tableName;
    }

    public static void beginTransaction() {
        Connection conn = getConnection();
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            LOGGER.error("Begin transaction failure", e);
            throw new RuntimeException(e);
        } finally {
            CONNECTION_HOLDER.set(conn);
        }
    }

    public static void commitTransaction() {
        Connection conn = getConnection();
        if (conn != null) {
            try {
                conn.commit();
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("Commit transaction failure", e);
                throw new RuntimeException(e);
            } finally {
                CONNECTION_HOLDER.remove();
            }
        }
    }

    public static void rollbackTransaction() {
        Connection conn = getConnection();
        if (conn != null) {
            try {
                conn.rollback();
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("Rollback transaction failure", e);
                throw new RuntimeException(e);
            } finally {
                CONNECTION_HOLDER.remove();
            }
        }
    }

    /**
     * author: Wangminze
     * date: 2016/12/31
     * description: 获取数据源,在smart-plugin-security中需要用到
     *
     * @return
     */
    public static DataSource getDataSource() {

        DataSource ds = new DataSource() {
            @Override
            public Connection getConnection() throws SQLException {
                return DATA_SOURCE.getConnection();
            }

            @Override
            public Connection getConnection(String username, String password) throws SQLException {
                return DATA_SOURCE.getConnection(username, password);
            }

            @Override
            public PrintWriter getLogWriter() throws SQLException {
                return DATA_SOURCE.getLogWriter();
            }

            @Override
            public void setLogWriter(PrintWriter out) throws SQLException {
                DATA_SOURCE.setLogWriter(out);
            }

            @Override
            public void setLoginTimeout(int seconds) throws SQLException {
                DATA_SOURCE.setLoginTimeout(seconds);
            }

            @Override
            public int getLoginTimeout() throws SQLException {
                return DATA_SOURCE.getLoginTimeout();
            }

            @Override
            public java.util.logging.Logger getParentLogger() throws SQLFeatureNotSupportedException {
                return DATA_SOURCE.getParentLogger();
            }

            @Override
            public <T> T unwrap(Class<T> iface) throws SQLException {
                return DATA_SOURCE.unwrap(iface);
            }

            @Override
            public boolean isWrapperFor(Class<?> iface) throws SQLException {
                return DATA_SOURCE.isWrapperFor(iface);
            }
        };
        return ds;
    }

    /**
     * author: Wangminze
     * date: 2017/1/7
     * description:  根据SQL和Param查询数据
     *
     * @param sql    SQL语句
     * @param params 参数，与SQL中的？匹配
     * @return 有多个值时只取第一个值，没有时返回NULL
     */
    public static <T> T query(String sql, Object... params) {
        T value = null;
        Set<T> values = querySet(sql, params);
        if (CollectionUtil.isNotEmpty(values)) {
            Iterator<T> iterator = values.iterator();
            if (iterator.hasNext()) {
                value = iterator.next();
            }
        }
        return value;
    }

    /**
     * author: Wangminze
     * date: 2017/1/7
     * description:  根据SQL和Param查询数据
     *
     * @param sql    SQL语句
     * @param params 参数，与SQL中的？匹配
     * @return 没有数据时，返回NULL
     */
    public static <T> Set<T> querySet(String sql, Object... params) {
        Set<T> values = null;
        ResultSetMetaData metaData = null;//获取 列信息

        try {
            Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            LOGGER.info(">>>>>>>>SQL=" + sql + ",params=" + params == null ? "" : params.toString());
            for (int i = 1; i <= params.length; i++) {
                ps.setObject(i, params[i - 1]);
            }
            ResultSet rs = ps.executeQuery();
            metaData = rs.getMetaData();
            int columns = metaData.getColumnCount();

            while (rs.next()) {
                values = new HashSet<T>();
                for (int i = 1; i <= columns; i++) {
                    values.add((T) rs.getObject(i));
                }
            }

        } catch (SQLException e) {
            LOGGER.error("Query failure", e);
            throw new RuntimeException(e);
        }/*finally{
            closeConnection();
        }*/
        return values;
    }
}
